CREATE trigger order_transfer
    on open_order
    after update
    as
begin
    declare @order_situation int,
        @timestamp varchar(50),
        @order_number varchar(20),
        @merchant_phone_number varchar(15),
        @dish_number varchar(20),
        @whole_num int,
        @finishTime date

    select @order_situation=order_situation from inserted
    if @order_situation=3-- 为3的时候代表订单已完成,其余的时候即使更新状态也不进行操作
        begin
            --将订单放入历史订单
            set @finishTime=getdate()
            insert into history_order(history_order_number,all_address,paytime,whole_price, customer_phone_number,merchant_phone_number)
            select open_order_number,all_address,paytime,whole_price, customer_phone_number,merchant_phone_number from inserted
            update history_order
            set finish_time=@finishTime
            where history_order_number in
                  (
                      select open_order_number from inserted
                  )

            select @order_number=(select open_order_number from inserted)
            insert into history_order_have(history_order_number,dish_number,whole_num)
            select open_order_number,dish_number,whole_num from open_order_have
            where open_order_number=@order_number

            delete from open_order_have
            where open_order_number=@order_number

            delete from open_order
            where open_order_number=@order_number



            --更新商家销量,即订单数
            select @merchant_phone_number=(select merchant_phone_number from inserted)
            update Merchant
            set sales_Volume=sales_Volume+1
            where merchant_phone_number=@merchant_phone_number

            --更新菜品销量,某一订单的菜品的数量
            declare C_num cursor fast_forward for
                select dish_number,whole_num
                from history_order_have
                where history_order_number=@order_number
            open C_num
            fetch next from C_num into @dish_number,@whole_num
            while @@FETCH_STATUS=0
                begin
                    update Dish
                    set dish_sales=dish_sales+@whole_num
                    where dish_number=@dish_number
                    fetch next from C_num into @dish_number,@whole_num
                end

            close C_num;

            deallocate C_num;
        end
end
go

